import os
from datetime import datetime

from flask import Blueprint, render_template, request, g, redirect, url_for, send_from_directory
from werkzeug.utils import secure_filename

from apps.desen.create_sql import Create_sql
from apps.desen.error_sql import Error_sql
from apps.desen.insert_sql import Insert_sql
from apps.desen.model import Desen
from ext import db
from settings import Config

allowed_extensions = ['xlsx']

desen_bp = Blueprint('desen', __name__, url_prefix='/desen')


# @desen_bp.before_app_request
# def desen_search():
#     page = request.args.get('page')
#     if not page:
#         page = 1
#     # 实现分页就要用到paginate()
#     pagination = Desen.query.order_by(-Desen.insert_time).paginate(page=int(page), per_page=10)
#     g.pagination = pagination


@desen_bp.route('/index', methods=['POST', 'GET'])
def desen_index():
    page = request.args.get('page')
    if not page:
        page = 1
    # 实现分页就要用到paginate()
    pagination = Desen.query.order_by(-Desen.insert_time).paginate(page=int(page), per_page=10)
    return render_template('index.html', pagination=pagination, search_flag='N')


@desen_bp.route('/update', methods=['POST', 'GET'])
def excel_upload():
    if request.method == 'POST':
        excel_file = request.files.get('excel_file')
        mission_name = request.form.get('mission_name')
        if excel_file:
            # 获取文件名
            excel_name = excel_file.filename
            if excel_name.rsplit('.')[-1] in allowed_extensions:
                secure_excle_name = secure_filename(excel_name)
                excel_path = os.path.join(Config.UPLOAD_EXCEL_DIR, secure_excle_name)
                # 保存文件到项目中
                excel_file.save(excel_path)
                # 获取文件地址
                excel_loc_path = os.path.join('..\\static\\excels\\', excel_name)
                create_sql_name = Create_sql(secure_excle_name)
                create_sql_path = os.path.join('..\\static\\sqls\\', create_sql_name)
                insert_sql_name = Insert_sql(secure_excle_name)
                insert_sql_path = os.path.join('..\\static\\sqls\\', insert_sql_name)
                error_sql_name = Error_sql()
                error_sql_path = os.path.join('..\\static\\sqls\\', error_sql_name)
                BASE_DIR = os.path.dirname(os.path.abspath('base_dir'))
                # 静态文件夹的路径,项目路径拼接向下的文件夹
                STATIC_DIR = os.path.join(BASE_DIR, 'static')
                sqls_path = os.path.join(STATIC_DIR, 'sqls')
                ct_txt_path = sqls_path + "\\create_sql.txt"
                ins_txt_path = sqls_path + "\\insert_sql.txt"
                desen = Desen()
                # 将记录插入到MySQL数据库中
                desen.mission_name = mission_name
                desen.excel_name = secure_excle_name
                desen.excel_loc = excel_loc_path
                desen.create_sql_name = create_sql_name
                desen.create_sql_loc = create_sql_path
                desen.insert_sql_name = insert_sql_name
                desen.insert_sql_loc = insert_sql_path
                if error_sql_name != 'no_error':
                    desen.error_sql_name = error_sql_name
                    desen.error_sql_loc = error_sql_path
                else:
                    desen.error_sql_name = 'no_error'
                    desen.error_sql_loc = 'no_error'
                db.session.add(desen)
                db.session.commit()
                # 删除文件校验临时文件
                os.remove(ct_txt_path)
                os.remove(ins_txt_path)
                print('ct_txt_path:' + ct_txt_path + ',ins_txt_path:' + ins_txt_path)
                return redirect(url_for('desen.desen_index'))
            else:
                return render_template('index.html', msg='上传文件不是xlsx文件', pagination=g.pagination)
        else:
            return render_template('index.html', msg='请上传文件', pagination=g.pagination)
    else:
        return render_template('index.html', msg='请求错误(get)', pagination=g.pagination)


@desen_bp.route('/file_delete')
def file_delete():
    file_id = request.args.get('file_id')
    desen = Desen.query.get(file_id)
    excel_loc = 'static/sqls/' + desen.excel_name
    create_loc = 'static/sqls/' + desen.create_sql_name
    insert_loc = 'static/sqls/' + desen.insert_sql_name
    error_loc = 'static/sqls/' + desen.error_sql_name
    os.remove(excel_loc)
    os.remove(create_loc)
    os.remove(insert_loc)
    os.remove(error_loc)
    db.session.delete(desen)
    db.session.commit()
    return redirect(url_for('desen.desen_index'))


@desen_bp.route('/search')
def mission_search():
    num = 0
    page = request.args.get('page')
    if not page:
        page = 1
    mission_name = request.args.get('mission_name')
    mission_date_start = request.args.get('mission_date_start')
    mission_date_end = request.args.get('mission_date_end')
    if str(mission_date_start).find(" ") > 0:
        mission_date_start += " 0:0:0"
    if str(mission_date_end).find(" ") > 0:
        mission_date_end += " 23:59:59"
    # print('mission_date_start:' + mission_date_start + ',mission_date_end:' + mission_date_end)
    # 传参计数
    if mission_name:
        num += 1
    if mission_date_start:
        num += 1
    if mission_date_end:
        num += 1

    # 根据参数数量不同判断
    if num == 0:
        print('0个参数')
        return redirect(url_for('desen.desen_index'))

    if num == 1:
        print('1个参数')
        if mission_name:
            pagination = Desen.query.filter(Desen.mission_name.like("%" + str(mission_name) + "%")).order_by(
                -Desen.insert_time).paginate(page=int(page), per_page=10)
            return render_template('index.html', pagination=pagination, search_flag='Y', mission_name=mission_name,
                                   mission_date_start=mission_date_start, mission_date_end=mission_date_end)
        if mission_date_start:
            pagination = Desen.query.filter(Desen.insert_time >= str(mission_date_start)).order_by(
                -Desen.insert_time).paginate(page=int(page), per_page=10)
            return render_template('index.html', pagination=pagination, search_flag='Y', mission_name=mission_name,
                                   mission_date_start=mission_date_start, mission_date_end=mission_date_end)
        if mission_date_end:
            pagination = Desen.query.filter(Desen.insert_time <= str(mission_date_end)).order_by(
                -Desen.insert_time).paginate(page=int(page), per_page=10)
            return render_template('index.html', pagination=pagination, search_flag='Y', mission_name=mission_name,
                                   mission_date_start=mission_date_start, mission_date_end=mission_date_end)

    if num == 2:
        print('2个参数')
        if mission_name and mission_date_start:
            pagination = Desen.query.filter(Desen.mission_name.like("%" + mission_name + "%"),
                                            Desen.insert_time >= str(mission_date_start)).order_by(
                -Desen.insert_time).paginate(page=int(page), per_page=10)
            return render_template('index.html', pagination=pagination, search_flag='Y', mission_name=mission_name,
                                   mission_date_start=mission_date_start, mission_date_end=mission_date_end)
        if mission_name and mission_date_end:
            pagination = Desen.query.filter(Desen.mission_name.like("%" + mission_name + "%"),
                                            Desen.insert_time <= str(mission_date_end)).order_by(
                -Desen.insert_time).paginate(page=int(page), per_page=10)
            return render_template('index.html', pagination=pagination, search_flag='Y', mission_name=mission_name,
                                   mission_date_start=mission_date_start, mission_date_end=mission_date_end)
        if mission_date_start and mission_date_end:
            pagination = Desen.query.filter(Desen.insert_time >= str(mission_date_start),
                                            Desen.insert_time <= str(mission_date_end)).order_by(
                -Desen.insert_time).paginate(page=int(page), per_page=10)
            return render_template('index.html', pagination=pagination, search_flag='Y', mission_name=mission_name,
                                   mission_date_start=mission_date_start, mission_date_end=mission_date_end)

    if num == 3:
        print('3个参数')
        pagination = Desen.query.order_by(Desen.mission_name.like("%" + mission_name + "%"),
                                          Desen.insert_time >= str(mission_date_start),
                                          Desen.insert_time <= str(mission_date_end),
                                          -Desen.insert_time).paginate(page=int(page), per_page=10)
        return render_template('index.html', pagination=pagination, search_flag='Y', mission_name=mission_name,
                               mission_date_start=mission_date_start, mission_date_end=mission_date_end)
